package com.ecsolutions.dao;

import com.ecsolutions.entity.Pledge_Entity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

/**
 * Created by Administrator on 2017/7/24.
 */
@Repository
public interface Pledge_DAO {
    // 证件类型
    @Select("SELECT distinct cstldes,cstcod2 FROM TACSTMAS WHERE codtype = 'ID'")
    @ResultType(HashMap.class)
    List<HashMap<String,String>> getIdType();

    //select 年收入币种
    @Select("select distinct ccy,ccyname from TACCYMAS")
    @ResultType(HashMap.class)
    List<HashMap<String,String>> getCcy();

    //select 保险公司名称
    @Select("select distinct name from InsuranceCompanyText")
    @ResultType(HashMap.class)
    List<HashMap<String,String>> getinsuranceList();

    // 贷款卡编码查询出的信息
    @Select("SELECT pIdTypeId as pidtypeid,lastName as mortgagorname,BorrowerContactAddress as contactaddress,BorrowerContactNumber as contactphone FROM LoanApplicant WHERE TRIM(ethnic) = TRIM(#{ethnic})")
    @ResultType(Pledge_Entity.class)
    Pledge_Entity getByEthnic(@Param("ethnic") String ethnic);

    // 客户号查询出的信息
    @Select("SELECT * FROM loanapplicantmortgage WHERE TRIM(custcod) = TRIM(#{custcod})")
    @ResultType(Pledge_Entity.class)
    List<Pledge_Entity> getByCustcod(@Param("custcod") String custcod);

    // ID查询出的信息
    @Select("SELECT * FROM loanapplicantmortgage WHERE TRIM(id) = TRIM(#{id})")
    @ResultType(Pledge_Entity.class)
    Pledge_Entity getById(@Param("id") String id);

    // line_no查询出List<Pledge_entit>信息
    @Select("SELECT * FROM loanapplicantmortgage WHERE TRIM(line_no) = TRIM(#{line_no})")
    @ResultType(Pledge_Entity.class)
    List<Pledge_Entity> getByLine_no(@Param("line_no") String line_no);

    // line_no查询出的最大num值
    @Select("SELECT Max(num) FROM loanapplicantmortgage WHERE TRIM(line_no) = TRIM(#{line_no})")
    @ResultType(int.class)
    int getByLineno(@Param("line_no") String line_no);

    // line_no和num查询出的id值
    @Select("SELECT id FROM loanapplicantmortgage WHERE TRIM(line_no) = TRIM(#{line_no}) and TRIM(num) = TRIM(#{num})")
    @ResultType(String.class)
    String getByLine_noAndNum(@Param("line_no") String line_no,@Param("num") int num);

    //保存抵押信息
    @Insert({"insert into loanapplicantmortgage(line_no,custcod,id,contractnumber,signeddate,mode1,begindate,enddate,originalvalueccy,originalvalue,marketvalueccy,marketvalue,assessedvalueccy,assessedvalue,amountccy,amount,scale,owner,isinsurance,assessmentagencies,assessmentdate,assessmentreportno,organizationcode,ethnic,pidtypeid,pidno,mortgagorname,contactaddress,contactphone,landuserightsno,landarea,landareaunit,landaddress,landaccessmethod,landnature,landhaveattachment,landhaveattachmentcertificate,landattachmentisconsistent,landisreg,landregorgans,landotherrightsno,landotherremark,estateno,estatearea,estateaddress,estatebuildingstructure,estatefloors,estateatfloor,estatebuildingdate,estateisreg,estateregorgans,estateotherrightsno,estaterightsaddress,estateattatichmentowner,estateremark,devicename,devicetype,deviceunit,devicenumber,deviceexfactorydate,deviceusedyears,deviceretirementyears,deviceremark,vehiclebrand,vehicleenginenumber,vehicletype,vehiclechassisnumber,vehiclebuydate,vehicleexpectedretirementdate,vehiclelicensenumber,vehicleremark,projectname,projectattathimentarea,projectbuildingarea,projectaddress,projectbuildingorg,projectbuildingfloors,projectbuildingdate,projectexpectedcompletiondate,projectconstructionunit,projectremark,shipbrand,shiptype,shiplicenenumber,shipexfactorydate,shipnavmil,shiprights,shipuse,shipremark,miningperson,mininglicencenumber,miningaddress,miningtype,miningname,miningworktype,miningproductionscale,miningarea,mininglastdate,miningremark,othername,otherunit,othernumber,otheraddress,otherremark,insuranceno,insuranceeffectivedate,insurancematuritydate,insurancetype,insurancecompany,insuranceamountccy,insuranceamount,insurancebeneficiaries,deviceaddress,vehiclerunlong,shipnumber,estatefloorsandatfloor,mortgagecustcod,num,stateid,createtime,refid)","values(#{line_no,jdbcType=VARCHAR},#{custcod,jdbcType=VARCHAR},emp_id_seq.nextval,#{contractnumber,jdbcType=VARCHAR},#{signeddate,jdbcType=TIMESTAMP},#{mode1,jdbcType=VARCHAR},#{begindate,jdbcType=TIMESTAMP},#{enddate,jdbcType=TIMESTAMP},#{originalvalueccy,jdbcType=CHAR},#{originalvalue,jdbcType=VARCHAR},#{marketvalueccy,jdbcType=CHAR},#{marketvalue,jdbcType=VARCHAR},#{assessedvalueccy,jdbcType=CHAR},#{assessedvalue,jdbcType=VARCHAR},#{amountccy,jdbcType=CHAR},#{amount,jdbcType=VARCHAR},#{scale,jdbcType=VARCHAR},#{owner,jdbcType=VARCHAR},#{isinsurance,jdbcType=VARCHAR},#{assessmentagencies,jdbcType=VARCHAR},#{assessmentdate,jdbcType=TIMESTAMP},#{assessmentreportno,jdbcType=VARCHAR},#{organizationcode,jdbcType=VARCHAR},#{ethnic,jdbcType=VARCHAR},#{pidtypeid,jdbcType=CHAR},#{pidno,jdbcType=VARCHAR},#{mortgagorname,jdbcType=VARCHAR},#{contactaddress,jdbcType=VARCHAR},#{contactphone,jdbcType=VARCHAR},#{landuserightsno,jdbcType=VARCHAR},#{landarea,jdbcType=NUMERIC},#{landareaunit,jdbcType=VARCHAR},#{landaddress,jdbcType=VARCHAR},#{landaccessmethod,jdbcType=NUMERIC},#{landnature,jdbcType=NUMERIC},#{landhaveattachment,jdbcType=NUMERIC},#{landhaveattachmentcertificate,jdbcType=NUMERIC},#{landattachmentisconsistent,jdbcType=NUMERIC},#{landisreg,jdbcType=NUMERIC},#{landregorgans,jdbcType=VARCHAR},#{landotherrightsno,jdbcType=VARCHAR},#{landotherremark,jdbcType=VARCHAR},#{estateno,jdbcType=VARCHAR},#{estatearea,jdbcType=NUMERIC},#{estateaddress,jdbcType=VARCHAR},#{estatebuildingstructure,jdbcType=NUMERIC},#{estatefloors,jdbcType=NUMERIC},#{estateatfloor,jdbcType=NUMERIC},#{estatebuildingdate,jdbcType=TIMESTAMP},#{estateisreg,jdbcType=NUMERIC},#{estateregorgans,jdbcType=VARCHAR},#{estateotherrightsno,jdbcType=VARCHAR},#{estaterightsaddress,jdbcType=VARCHAR},#{estateattatichmentowner,jdbcType=VARCHAR},#{estateremark,jdbcType=VARCHAR},#{devicename,jdbcType=VARCHAR},#{devicetype,jdbcType=VARCHAR},#{deviceunit,jdbcType=VARCHAR},#{devicenumber,jdbcType=VARCHAR},#{deviceexfactorydate,jdbcType=TIMESTAMP},#{deviceusedyears,jdbcType=NUMERIC},#{deviceretirementyears,jdbcType=NUMERIC},#{deviceremark,jdbcType=VARCHAR},#{vehiclebrand,jdbcType=VARCHAR},#{vehicleenginenumber,jdbcType=VARCHAR},#{vehicletype,jdbcType=VARCHAR},#{vehiclechassisnumber,jdbcType=VARCHAR},#{vehiclebuydate,jdbcType=TIMESTAMP},#{vehicleexpectedretirementdate,jdbcType=TIMESTAMP},#{vehiclelicensenumber,jdbcType=VARCHAR},#{vehicleremark,jdbcType=VARCHAR},#{projectname,jdbcType=VARCHAR},#{projectattathimentarea,jdbcType=NUMERIC},#{projectbuildingarea,jdbcType=NUMERIC},#{projectaddress,jdbcType=VARCHAR},#{projectbuildingorg,jdbcType=VARCHAR},#{projectbuildingfloors,jdbcType=NUMERIC},#{projectbuildingdate,jdbcType=TIMESTAMP},#{projectexpectedcompletiondate,jdbcType=TIMESTAMP},#{projectconstructionunit,jdbcType=VARCHAR},#{projectremark,jdbcType=VARCHAR},#{shipbrand,jdbcType=VARCHAR},#{shiptype,jdbcType=VARCHAR},#{shiplicenenumber,jdbcType=VARCHAR},#{shipexfactorydate,jdbcType=TIMESTAMP},#{shipnavmil,jdbcType=NUMERIC},#{shiprights,jdbcType=NUMERIC},#{shipuse,jdbcType=NUMERIC},#{shipremark,jdbcType=VARCHAR},#{miningperson,jdbcType=VARCHAR},#{mininglicencenumber,jdbcType=VARCHAR},#{miningaddress,jdbcType=VARCHAR},#{miningtype,jdbcType=VARCHAR},#{miningname,jdbcType=VARCHAR},#{miningworktype,jdbcType=VARCHAR},#{miningproductionscale,jdbcType=NUMERIC},#{miningarea,jdbcType=NUMERIC},#{mininglastdate,jdbcType=TIMESTAMP},#{miningremark,jdbcType=VARCHAR},#{othername,jdbcType=VARCHAR},#{otherunit,jdbcType=VARCHAR},#{othernumber,jdbcType=NUMERIC},#{otheraddress,jdbcType=VARCHAR},#{otherremark,jdbcType=VARCHAR},#{insuranceno,jdbcType=VARCHAR},#{insuranceeffectivedate,jdbcType=TIMESTAMP},#{insurancematuritydate,jdbcType=TIMESTAMP},#{insurancetype,jdbcType=VARCHAR},#{insurancecompany,jdbcType=VARCHAR},#{insuranceamountccy,jdbcType=CHAR},#{insuranceamount,jdbcType=NUMERIC},#{insurancebeneficiaries,jdbcType=VARCHAR},#{deviceaddress,jdbcType=VARCHAR},#{vehiclerunlong,jdbcType=NUMERIC},#{shipnumber,jdbcType=NUMERIC},#{estatefloorsandatfloor,jdbcType=VARCHAR},#{mortgagecustcod,jdbcType=VARCHAR},#{num,jdbcType=NUMERIC},#{stateid,jdbcType=NUMERIC},#{createtime,jdbcType=TIMESTAMP},#{refid,jdbcType=CHAR})"})
    void savePledgeInfo(Pledge_Entity pledge_entity);

    //update抵押信息
    @Update("update loanapplicantmortgage set contractnumber=#{contractnumber,jdbcType=VARCHAR},signeddate=#{signeddate,jdbcType=TIMESTAMP},mode1=#{mode1,jdbcType=VARCHAR},begindate=#{begindate,jdbcType=TIMESTAMP},enddate=#{enddate,jdbcType=TIMESTAMP},originalvalueccy=#{originalvalueccy,jdbcType=CHAR},originalvalue=#{originalvalue,jdbcType=VARCHAR},marketvalueccy=#{marketvalueccy,jdbcType=CHAR},marketvalue=#{marketvalue,jdbcType=VARCHAR},assessedvalueccy=#{assessedvalueccy,jdbcType=CHAR},assessedvalue=#{assessedvalue,jdbcType=VARCHAR},amountccy=#{amountccy,jdbcType=CHAR},amount=#{amount,jdbcType=VARCHAR},scale=#{scale,jdbcType=VARCHAR},owner=#{owner,jdbcType=VARCHAR},isinsurance=#{isinsurance,jdbcType=VARCHAR},assessmentagencies=#{assessmentagencies,jdbcType=VARCHAR},assessmentdate=#{assessmentdate,jdbcType=TIMESTAMP},assessmentreportno=#{assessmentreportno,jdbcType=VARCHAR},organizationcode=#{organizationcode,jdbcType=VARCHAR},ethnic=#{ethnic,jdbcType=VARCHAR},pidtypeid=#{pidtypeid,jdbcType=CHAR},pidno=#{pidno,jdbcType=VARCHAR},mortgagorname=#{mortgagorname,jdbcType=VARCHAR},contactaddress=#{contactaddress,jdbcType=VARCHAR},contactphone=#{contactphone,jdbcType=VARCHAR},landuserightsno=#{landuserightsno,jdbcType=VARCHAR},landarea=#{landarea,jdbcType=NUMERIC},landareaunit=#{landareaunit,jdbcType=VARCHAR},landaddress=#{landaddress,jdbcType=VARCHAR},landaccessmethod=#{landaccessmethod,jdbcType=NUMERIC},landnature=#{landnature,jdbcType=NUMERIC},landhaveattachment=#{landhaveattachment,jdbcType=NUMERIC},landhaveattachmentcertificate=#{landhaveattachmentcertificate,jdbcType=NUMERIC},landattachmentisconsistent=#{landattachmentisconsistent,jdbcType=NUMERIC},landisreg=#{landisreg,jdbcType=NUMERIC},landregorgans=#{landregorgans,jdbcType=VARCHAR},landotherrightsno=#{landotherrightsno,jdbcType=VARCHAR},landotherremark=#{landotherremark,jdbcType=VARCHAR},estateno=#{estateno,jdbcType=VARCHAR},estatearea=#{estatearea,jdbcType=NUMERIC},estateaddress=#{estateaddress,jdbcType=VARCHAR},estatebuildingstructure=#{estatebuildingstructure,jdbcType=NUMERIC},estatefloors=#{estatefloors,jdbcType=NUMERIC},estateatfloor=#{estateatfloor,jdbcType=NUMERIC},estatebuildingdate=#{estatebuildingdate,jdbcType=TIMESTAMP},estateisreg=#{estateisreg,jdbcType=NUMERIC},estateregorgans=#{estateregorgans,jdbcType=VARCHAR},estateotherrightsno=#{estateotherrightsno,jdbcType=VARCHAR},estaterightsaddress=#{estaterightsaddress,jdbcType=VARCHAR},estateattatichmentowner=#{estateattatichmentowner,jdbcType=VARCHAR},estateremark=#{estateremark,jdbcType=VARCHAR},devicename=#{devicename,jdbcType=VARCHAR},devicetype=#{devicetype,jdbcType=VARCHAR},deviceunit=#{deviceunit,jdbcType=VARCHAR},devicenumber=#{devicenumber,jdbcType=VARCHAR},deviceexfactorydate=#{deviceexfactorydate,jdbcType=TIMESTAMP},deviceusedyears=#{deviceusedyears,jdbcType=NUMERIC},deviceretirementyears=#{deviceretirementyears,jdbcType=NUMERIC},deviceremark=#{deviceremark,jdbcType=VARCHAR},vehiclebrand=#{vehiclebrand,jdbcType=VARCHAR},vehicleenginenumber=#{vehicleenginenumber,jdbcType=VARCHAR},vehicletype=#{vehicletype,jdbcType=VARCHAR},vehiclechassisnumber=#{vehiclechassisnumber,jdbcType=VARCHAR},vehiclebuydate=#{vehiclebuydate,jdbcType=TIMESTAMP},vehicleexpectedretirementdate=#{vehicleexpectedretirementdate,jdbcType=TIMESTAMP},vehiclelicensenumber=#{vehiclelicensenumber,jdbcType=VARCHAR},vehicleremark=#{vehicleremark,jdbcType=VARCHAR},projectname=#{projectname,jdbcType=VARCHAR},projectattathimentarea=#{projectattathimentarea,jdbcType=NUMERIC},projectbuildingarea=#{projectbuildingarea,jdbcType=NUMERIC},projectaddress=#{projectaddress,jdbcType=VARCHAR},projectbuildingorg=#{projectbuildingorg,jdbcType=VARCHAR},projectbuildingfloors=#{projectbuildingfloors,jdbcType=NUMERIC},projectbuildingdate=#{projectbuildingdate,jdbcType=TIMESTAMP},projectexpectedcompletiondate=#{projectexpectedcompletiondate,jdbcType=TIMESTAMP},projectconstructionunit=#{projectconstructionunit,jdbcType=VARCHAR},projectremark=#{projectremark,jdbcType=VARCHAR},shipbrand=#{shipbrand,jdbcType=VARCHAR},shiptype=#{shiptype,jdbcType=VARCHAR},shiplicenenumber=#{shiplicenenumber,jdbcType=VARCHAR},shipexfactorydate=#{shipexfactorydate,jdbcType=TIMESTAMP},shipnavmil=#{shipnavmil,jdbcType=NUMERIC},shiprights=#{shiprights,jdbcType=NUMERIC},shipuse=#{shipuse,jdbcType=NUMERIC},shipremark=#{shipremark,jdbcType=VARCHAR},miningperson=#{miningperson,jdbcType=VARCHAR},mininglicencenumber=#{mininglicencenumber,jdbcType=VARCHAR},miningaddress=#{miningaddress,jdbcType=VARCHAR},miningtype=#{miningtype,jdbcType=VARCHAR},miningname=#{miningname,jdbcType=VARCHAR},miningworktype=#{miningworktype,jdbcType=VARCHAR},miningproductionscale=#{miningproductionscale,jdbcType=NUMERIC},miningarea=#{miningarea,jdbcType=NUMERIC},mininglastdate=#{mininglastdate,jdbcType=TIMESTAMP},miningremark=#{miningremark,jdbcType=VARCHAR},othername=#{othername,jdbcType=VARCHAR},otherunit=#{otherunit,jdbcType=VARCHAR},othernumber=#{othernumber,jdbcType=NUMERIC},otheraddress=#{otheraddress,jdbcType=VARCHAR},otherremark=#{otherremark,jdbcType=VARCHAR},insuranceno=#{insuranceno,jdbcType=VARCHAR},insuranceeffectivedate=#{insuranceeffectivedate,jdbcType=TIMESTAMP},insurancematuritydate=#{insurancematuritydate,jdbcType=TIMESTAMP},insurancetype=#{insurancetype,jdbcType=VARCHAR},insurancecompany=#{insurancecompany,jdbcType=VARCHAR},insuranceamountccy=#{insuranceamountccy,jdbcType=CHAR},insuranceamount=#{insuranceamount,jdbcType=NUMERIC},insurancebeneficiaries=#{insurancebeneficiaries,jdbcType=VARCHAR},deviceaddress=#{deviceaddress,jdbcType=VARCHAR},vehiclerunlong=#{vehiclerunlong,jdbcType=NUMERIC},shipnumber=#{shipnumber,jdbcType=NUMERIC},estatefloorsandatfloor=#{estatefloorsandatfloor,jdbcType=VARCHAR},mortgagecustcod=#{mortgagecustcod,jdbcType=VARCHAR},num=#{num,jdbcType=NUMERIC},stateid=#{stateid,jdbcType=NUMERIC},createtime=#{createtime,jdbcType=TIMESTAMP},refid=#{refid,jdbcType=CHAR} where id=#{id}")
    void updatePledgeInfo(Pledge_Entity pledge_entity);

    //删除抵押信息
    @Delete("delete from loanapplicantmortgage where id=#{id}")
    void deletePledgeInfo(String id);
}
